﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;

namespace Wjtour.BLL
{
    public class gallery_bll : base_dll
    {
        public int GetNextGalleryId(int galleryid)
        {
            string sql = "select top 1 id from gallery where id > " + galleryid;
            DbCommand cmd = DbAccess.CreateCommand();
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;

            return Convert.ToInt32(DbAccess.ExecuteScalar(cmd));
        }

        public int GetPrevGalleryId(int galleryid)
        {
            string sql = "select top 1 id from gallery where id < " + galleryid;
            DbCommand cmd = DbAccess.CreateCommand();
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;

            return Convert.ToInt32(DbAccess.ExecuteScalar(cmd));
        }

        public List<Wjtour.Domain.dto.sitepicture_picturetype> GetGalleryPicturesList(int galleryid)
        {
            string where = " where g.id=" + galleryid;
            string sql = string.Format(@"select a.*,b.picturetypename,g.title galleryname,g.createdate galleryaddtime from sitepicture a
                inner join picturetype b on a.picturetypeid = b.id
                left join gallery g on a.galleryid = g.id
                {0}
                order by a.id desc", where);
            return GetAll<Wjtour.Domain.dto.sitepicture_picturetype>(sql).ToList();
        }

        public Wjtour.Domain.gallery Load(int id)
        {
            string sql = "select * from gallery where id=" + id;
            return DbAccess.GetObjects<Wjtour.Domain.gallery>(sql).FirstOrDefault();
        }

        public bool Save(Wjtour.Domain.gallery entity)
        {
            string sql = "";

            DbCommand cmd = DbAccess.CreateCommand();

            if (entity.id > 0)
            {
                sql = @"UPDATE gallery SET 
                title = @title,
                gallerydesc = @gallerydesc,
                picturetypeid = @picturetypeid,
                createdate = @createdate,
                memberid = @memberid,
                isaudit = @isaudit
                 WHERE id=@id";
                DbAccess.AddInParameter(cmd, "@id", DbType.Int32, entity.id);

            }
            else
            {
                sql = @"INSERT INTO gallery( 
                title,gallerydesc,picturetypeid,createdate,memberid,isaudit)VALUES(
                @title,@gallerydesc,@picturetypeid,@createdate,@memberid,@isaudit)";
            }
            DbAccess.AddInParameter(cmd, "@title", DbType.String, entity.title);
            DbAccess.AddInParameter(cmd, "@gallerydesc", DbType.String, entity.gallerydesc);
            DbAccess.AddInParameter(cmd, "@picturetypeid", DbType.Int32, entity.picturetypeid);
            DbAccess.AddInParameter(cmd, "@createdate", DbType.DateTime, entity.createdate);
            DbAccess.AddInParameter(cmd, "@memberid", DbType.Int32, entity.memberid);
            DbAccess.AddInParameter(cmd, "@isaudit", DbType.Int32, entity.isaudit);
            cmd.CommandText = sql;
            cmd.CommandType = System.Data.CommandType.Text;
            return DbAccess.ExecuteNonQuery(cmd) > 0;
        }

        public bool Save(Wjtour.Domain.gallery entity, out int id)
        {
            string sql = "";

            DbCommand cmd = DbAccess.CreateCommand();

            if (entity.id > 0)
            {
                sql = @"UPDATE gallery SET 
                title = @title,
                gallerydesc = @gallerydesc,
                picturetypeid = @picturetypeid,
                createdate = @createdate,
                memberid = @memberid,
                isaudit = @isaudit
                 WHERE id=@id";
                DbAccess.AddInParameter(cmd, "@id", DbType.Int32, entity.id);

            }
            else
            {
                sql = @"INSERT INTO gallery( 
                title,gallerydesc,picturetypeid,createdate,memberid,isaudit)VALUES(
                @title,@gallerydesc,@picturetypeid,@createdate,@memberid,@isaudit);
                select @id = @@IDENTITY";
                DbAccess.AddOutParameter(cmd, DbType.Int32, "@id");
            }
            DbAccess.AddInParameter(cmd, "@title", DbType.String, entity.title);
            DbAccess.AddInParameter(cmd, "@gallerydesc", DbType.String, entity.gallerydesc);
            DbAccess.AddInParameter(cmd, "@picturetypeid", DbType.Int32, entity.picturetypeid);
            DbAccess.AddInParameter(cmd, "@createdate", DbType.DateTime, entity.createdate);
            DbAccess.AddInParameter(cmd, "@memberid", DbType.Int32, entity.memberid);
            DbAccess.AddInParameter(cmd, "@isaudit", DbType.Int32, entity.isaudit);
            cmd.CommandText = sql;
            cmd.CommandType = System.Data.CommandType.Text;
            bool ret = DbAccess.ExecuteNonQuery(cmd) > 0;
            if (ret)
            {
                id = Convert.ToInt32(cmd.Parameters["@id"].Value);
            }
            else id = 0;
            return ret;
        }

        public int BatchDelete(string ids)
        {
            string sql = "delete from gallery where id in(" + ids + ")";
            return DbAccess.ExecuteNonQuery(sql);
        }
    }
}
